package com.gitee.lw114514;
import java.util.ArrayList;
import java.util.Scanner;
import java.sql.*;

//TIP 要<b>运行</b>代码，请按 <shortcut actionId="Run"/> 或
// 点击装订区域中的 <icon src="AllIcons.Actions.Execute"/> 图标。
public class Main {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        System.out.println("欢迎来到我的书城！");
        ArrayList BookList = new ArrayList();
        while (true) {
            System.out.println("1.展示所有书籍");
            System.out.println("2.根据序列号查询单本书籍");
            System.out.println("3.上新书籍");
            System.out.println("4.下架书籍");
            System.out.println("5.退出程序");
            System.out.println("请输入>");

            //加载接受输入的scanner类
            Scanner sc = new Scanner(System.in);

            int choice = sc.nextInt();//键盘输入数据后点击回车，系统即接收到该数字

            if (choice == 1) {
                //展示所有的书籍
                System.out.println("------>展示全部书籍");
                ArrayList AllBooklst = QueryAllBook();
                for (int i = 0; i < AllBooklst.size(); i++) {
                    Book tempBook = (Book) AllBooklst.get(i);
                    System.out.println(tempBook.getbID()
                            + "---"
                            + tempBook.getbName()
                            + "---"
                            + tempBook.getbAuthor()
                            + "--- ￥"
                            + tempBook.getbPrice());
                }
                System.out.println("----->>=<<-----");
            }

            if(choice == 2){
                //根据书籍id查询单本书籍
                System.out.println("------>展示单本书籍");
                System.out.println("------>请输入要查询的书籍的id>");
                int bno = sc.nextInt();
                Book returnBook = QuerySingleBook(bno);
                if(returnBook != null){
                    System.out.println("查询结果：");
                    System.out.println(returnBook.getbID()
                            + "---"
                            + returnBook.getbName()
                            + "---"
                            + returnBook.getbAuthor()
                            + "--- ￥"
                            + returnBook.getbPrice());
                }else{
                    System.out.println("未查询到任何结果！");
                }
            }


            if (choice == 3) {
                //上新书籍
                System.out.println("------>上新书籍");
                System.out.println("请输入书籍编号>");
                int id = sc.nextInt();
                System.out.println("请输入书籍名>");
                String name = sc.next();
                System.out.println("请输入书籍作者>");
                String author = sc.next();
                System.out.println("请输入书籍价格>");
                int price = sc.nextInt();

                Book b = new Book(name, author, id, price);
                //BookList.add(b);

                boolean flag = addNewBook(b);
                if(flag){
                    System.out.println("------>书籍上新成功！");
                }else{
                    System.out.println("------>书籍上新失败！");
                }
            }

            if (choice == 4) {
                //下架书籍
                System.out.println("------>下架书籍");
                System.out.println("请输入你要下架的书籍id>");
                int del_id = sc.nextInt();

                boolean flag = delBook(del_id);

                if(flag){
                    System.out.println("成功删除书籍！");
                }else {
                    System.out.println("删除失败！");
                }
            }

            if (choice == 5) {
                //退出程序
                System.out.println("------>程序已退出");
                break;
            }
        }
    }

    public static ArrayList QueryAllBook() throws SQLException, ClassNotFoundException {
        try {
            Connection conn = ConnectSQL();
            Statement sta = conn.createStatement(); //创建对话

            //发送查询命令
            ResultSet resultSet = sta.executeQuery("SELECT * FROM t_book;");
            ArrayList bklst = new ArrayList<Book>();
            while (resultSet.next()) {
                //循环输出
            /*
            System.out.println(resultSet.getInt("id")
                    + "---"
                    + resultSet.getString("name")
                    + "---"
                    + resultSet.getString("author")
                    + " ￥"
                    + resultSet.getInt("price"));
             */
                Book tempBook = new Book(resultSet.getString("name"),
                        resultSet.getString("author"),
                        resultSet.getInt("id"),
                        resultSet.getInt("price"));

                bklst.add(tempBook);
            }
            conn.close();
            return bklst;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }


    }

    public static Book QuerySingleBook(int bno){
        //链接数据库
        try{
            Connection conn = ConnectSQL();
            Statement sta = conn.createStatement(); //创建对话

            //发送查询命令
            ResultSet resultSet = sta.executeQuery("SELECT * FROM t_book WHERE id=" + bno);

            // 检查是否有结果，并移动到第一条记录
            if (resultSet.next()) {
                Book tempBook = new Book(resultSet.getString("name"),
                        resultSet.getString("author"),
                        resultSet.getInt("id"),
                        resultSet.getInt("price"));
                conn.close();
                return tempBook;
            } else {
                return null; // 没有找到对应的书籍
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static boolean addNewBook(Book book) {
        String sql = "INSERT INTO t_book (id, name, author, price) VALUES (?, ?, ?, ?)";

        try (Connection conn = ConnectSQL();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, book.getbID());
            pstmt.setString(2, book.getbName());
            pstmt.setString(3, book.getbAuthor());
            pstmt.setInt(4, book.getbPrice());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("添加书籍失败: " + e.getMessage());
            return false;
        }
    }

    public static boolean delBook(int bno) throws SQLException, ClassNotFoundException {
        String sql = "DELETE FROM t_book WHERE id=?";

        try (Connection conn = ConnectSQL();
             PreparedStatement pstmt = conn.prepareStatement(sql)){
            pstmt.setInt(1, bno);

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.out.println("删除书籍失败: " + e.getMessage());
            return false;
        }
    }


    public static Connection ConnectSQL() throws ClassNotFoundException, SQLException {
        //链接数据库的专用函数
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获得数据库链接
        String url = "jdbc:mysql://127.0.0.1:3306/test01?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "jvnanji";
        String passwd = "qaq123456";
        //获取链接

        //创建会话(state通俗理解就是sql命令发送器)
        return DriverManager.getConnection(url, username, passwd);
    }

}